﻿using KPIS.DBM;
using KPIS.GERP.GBMS.APP_UI.Common;
using KPIS.GERP.GBMS.BLL;
using KPIS.GERP.GBMS.MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace KPIS.GERP.GBMS.APP_UI.Form
{
    public partial class BGM_ProjectOptionSummary : System.Web.UI.Page
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        private IProject projectBLL;
        private string year;
        private string type;
        private string strOptionType;
        private ComboBLL comboBLL = new ComboBLL();
        Main main = new Main();
        private SystemControlBLL scBLL = new SystemControlBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            type = Request.QueryString["type"].ToString();
            year = Request.QueryString["year"].ToString();
            strOptionType = Request.QueryString["seq"].ToString();

            if (!IsPostBack)
            {
                LoadJobSiteCombo();
                LoadChart();
            }
        }

        protected void LoadChart()
        {
            //projectBLL = Project_Factory.GetProjectFactory(type);
            string strJobSiteSeq = ddlJobSite.SelectedValue;
            DataTable dtb = null;

            if (rblChartType.SelectedValue == "Bar")
            {
                BarChart.Visible = true;
                PieChart.Visible = false;

                dtb = ListProjectOption(strOptionType, year, strJobSiteSeq);

                BarChart.DataSource = dtb;
                BarChart.DataBind();
            }
            else
            {
                BarChart.Visible = false;
                PieChart.Visible = true;

                dtb = ListProjectOptionPie(strOptionType, year, strJobSiteSeq);

                PieChart.DataSource = dtb;
                PieChart.DataBind();
            }
        }

        private void LoadJobSiteCombo()
        {
            string jobSiteID = (string)new SsMng().GetUss(SsMng.SsUserType.WorkingJobSiteSeq);
            string viewOnly = Request.QueryString["type"];
            DataTable dt = comboBLL.ListJobsite();
            ddlJobSite.DataSource = dt;
            ddlJobSite.DataBind();

            main.InsertEmptyDDLAll(ddlJobSite);

            ddlJobSite.SelectedValue = jobSiteID;
            if ((jobSiteID != scBLL.JobSiteStrategyID() && jobSiteID != scBLL.JobSiteFinanceID()) && jobSiteID != "")
            {
                ddlJobSite.Enabled = false;
            }
            else
            {
                ddlJobSite.Enabled = true;
            }
        }

        private DataTable ListProjectOption(string optionType, string bgm_year, string strJobSiteSeq)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@type", optionType);
                dbManager.AddParameters(2, "@year", bgm_year);
                dbManager.AddParameters(3, "@job_site_seq", strJobSiteSeq);

                string sql = "select o.bgm_option_seq, o.bgm_option_name, ot.bgm_option_type_name"
                    + ", sum(coalesce(pe.total_amount, 0)) as total_amount"
                    + " from bgm_project_option re"
                    + " left join bgm_mas_option o on re.bgm_option_seq = o.bgm_option_seq"
                    + " left join bgm_project_expense pe on re.bgm_proj_seq = pe.bgm_proj_seq"
                    + " left join bgm_project p on re.bgm_proj_seq = p.bgm_proj_seq"
                    + " left join bgm_system_option_type ot on o.bgm_option_type_seq = ot.bgm_option_type_seq"

                    + " where o.bgm_option_type_seq = @type"
                    + (bgm_year.Length > 0 ? " and p.bgm_year = @year" : "")
                    + (strJobSiteSeq != null && strJobSiteSeq.Length > 0 ? " and p.job_site_seq = @job_site_seq" : "")
                    + " group by o.bgm_option_seq, o.bgm_option_name, ot.bgm_option_type_name"
                    + " order by o.bgm_option_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    BarChart.ChartTitle.Text = "แผนภูมิแสดงข้อมูลเงินงบประมาณประจำปี " + bgm_year + " จำแนกตาม" + dt.Rows[0]["bgm_option_type_name"].ToString();
                    BarChart.PlotArea.XAxis.TitleAppearance.Text = dt.Rows[0]["bgm_option_type_name"].ToString();
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        private DataTable ListProjectOptionPie(string optionType, string bgm_year, string strJobSiteSeq)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@type", optionType);
                dbManager.AddParameters(2, "@year", bgm_year);
                dbManager.AddParameters(3, "@job_site_seq", strJobSiteSeq);

                //string sql = "select o.bgm_option_seq, o.bgm_option_name, ot.bgm_option_type_name"
                  //    + ", sum(coalesce(pe.total_amount, 0)) as total_amount"
                //    + " from bgm_project_option re"
                //    + " left join bgm_mas_option o on re.bgm_option_seq = o.bgm_option_seq"
                //    + " left join bgm_project_expense pe on re.bgm_proj_seq = pe.bgm_proj_seq"
                //    + " left join bgm_project p on re.bgm_proj_seq = p.bgm_proj_seq"
                //    + " left join bgm_system_option_type ot on o.bgm_option_type_seq = ot.bgm_option_type_seq"

                //    + " where o.bgm_option_type_seq = @type"
                //    + (bgm_year.Length > 0 ? " and p.bgm_year = @year" : "")
                //    + " group by o.bgm_option_seq, o.bgm_option_name, ot.bgm_option_type_name"
                //    + " order by o.bgm_option_name";

                string sql = "select *"
                    + ", ROUND(total_amount * 100 / sum_total_amount, 2) as percents"
                    + ", total_amount AS format_total_amount"
                    + " from ("
                    + " select o.bgm_option_seq, o.bgm_option_name, ot.bgm_option_type_name"
                        + ", sum(coalesce(pe.total_amount, 0)) as total_amount"
                        + ", (select sum(coalesce(pe.total_amount, 0))"
	                        + " from bgm_project_option re"
	                        + " left join bgm_mas_option o on re.bgm_option_seq = o.bgm_option_seq"
	                        + " left join bgm_project_expense pe on re.bgm_proj_seq = pe.bgm_proj_seq"
	                        + " left join bgm_project p on re.bgm_proj_seq = p.bgm_proj_seq "
	                        + " left join bgm_system_option_type ot on o.bgm_option_type_seq = ot.bgm_option_type_seq"
	                        + " where o.bgm_option_type_seq = @type"
                            + (bgm_year.Length > 0 ? " and p.bgm_year = @year" : "")
	                    + ") as sum_total_amount"
                    + " from bgm_project_option re"
                    + " left join bgm_mas_option o on re.bgm_option_seq = o.bgm_option_seq"
                    + " left join bgm_project_expense pe on re.bgm_proj_seq = pe.bgm_proj_seq"
                    + " left join bgm_project p on re.bgm_proj_seq = p.bgm_proj_seq"
                    + " left join bgm_system_option_type ot on o.bgm_option_type_seq = ot.bgm_option_type_seq"
                    + " where o.bgm_option_type_seq = @type"
                    + (bgm_year.Length > 0 ? " and p.bgm_year = @year" : "")
                    + (strJobSiteSeq != null && strJobSiteSeq.Length > 0 ? " and p.job_site_seq = @job_site_seq" : "")
                    + " group by o.bgm_option_seq, o.bgm_option_name, ot.bgm_option_type_name"
                    //+ "  order by o.bgm_option_name)"
                    + ")t";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    PieChart.ChartTitle.Text = "แผนภูมิแสดงข้อมูลเงินงบประมาณประจำปี " + bgm_year + " จำแนกตาม" + dt.Rows[0]["bgm_option_type_name"].ToString();
                    //PieChart.PlotArea.XAxis.TitleAppearance.Text = dt.Rows[0]["bgm_option_type_name"].ToString();
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        protected void rblChartType_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadChart();
        }

        protected void ddlJobSite_SelectedIndexChanged(object sender, Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)
        {
            LoadChart();
        }
    }
}